import mysql.connector
from app import db_config
from werkzeug.security import generate_password_hash

def init_db():
    # 创建数据库连接
    conn = mysql.connector.connect(
        host=db_config['host'],
        user=db_config['user'],
        password=db_config['password']
    )
    cursor = conn.cursor()

    # 创建数据库
    cursor.execute(f"DROP DATABASE IF EXISTS {db_config['database']}")
    cursor.execute(f"CREATE DATABASE {db_config['database']}")
    cursor.execute(f"USE {db_config['database']}")

    # 创建用户表
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS users (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            email VARCHAR(100) UNIQUE NOT NULL,
            password VARCHAR(255) NOT NULL,
            role ENUM('admin', 'volunteer', 'organization') NOT NULL,
            status ENUM('active', 'pending', 'suspended') DEFAULT 'pending',
            phone VARCHAR(20),
            birth_date DATE,
            gender ENUM('male', 'female', 'other'),
            address TEXT,
            skills TEXT,
            introduction TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        )
    """)

    # 创建活动表
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS activities (
            id INT AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(200) NOT NULL,
            description TEXT,
            organization_id INT,
            category ENUM('education', 'environment', 'healthcare', 'elderly', 'other') NOT NULL,
            start_date DATETIME NOT NULL,
            end_date DATETIME NOT NULL,
            location VARCHAR(200) NOT NULL,
            required_skills TEXT,
            max_participants INT,
            current_participants INT DEFAULT 0,
            status ENUM('pending', 'approved', 'rejected', 'completed') DEFAULT 'pending',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (organization_id) REFERENCES users(id)
        )
    """)

    # 创建活动报名表
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS registrations (
            id INT AUTO_INCREMENT PRIMARY KEY,
            activity_id INT,
            volunteer_id INT,
            status ENUM('pending', 'approved', 'rejected', 'completed') DEFAULT 'pending',
            feedback TEXT,
            feedback_time TIMESTAMP NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (activity_id) REFERENCES activities(id),
            FOREIGN KEY (volunteer_id) REFERENCES users(id)
        )
    """)

    # 创建通知表
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS notifications (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT,
            content TEXT NOT NULL,
            is_read BOOLEAN DEFAULT FALSE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id)
        )
    """)

    # 生成管理员密码哈希，使用 pbkdf2:sha256 方法
    admin_password_hash = generate_password_hash('admin123', method='pbkdf2:sha256')

    # 插入默认管理员账户
    cursor.execute("""
        INSERT INTO users (name, email, password, role, status)
        VALUES ('Admin', 'admin@example.com', %s, 'admin', 'active')
    """, (admin_password_hash,))

    conn.commit()
    cursor.close()
    conn.close()
    print("数据库初始化完成！")
    print("管理员账户:")
    print("- 邮箱: admin@example.com")
    print("- 密码: admin123")

if __name__ == '__main__':
    init_db() 